clear
set matsize 5000
cd <directory>
**********************************************************************************************************************
** Takes the raw attendance files (daily) and creates clean attendance variables
**********************************************************************************************************************

set more off
foreach x in jan feb mar apr may jun jul aug sep oct nov dec{

foreach j of numlist 2010 {
import excel "eas_`x'`j'.xls", sheet("Sheet1") firstrow clear

rename K `x'1
rename L `x'2
rename M `x'3
rename N `x'4
rename O `x'5
rename P `x'6
rename Q `x'7
rename R `x'8
rename S `x'9
rename T `x'10
rename U `x'11
rename V `x'12
rename W `x'13
rename X `x'14
rename Y `x'15
rename Z `x'16
rename AA `x'17
rename AB `x'18
rename AC `x'19
rename AD `x'20
rename AE `x'21
rename AF `x'22
rename AG `x'23
rename AH `x'24
rename AI `x'25
rename AJ `x'26
rename AK `x'27
rename AL `x'28
 rename AM `x'29
 rename AN `x'30
 rename AO `x'31

tostring `x'29 `x'30 `x'31, replace

reshape long `x', i( UNIT TKNNO) j(date)
rename `x' attendance
g month="`x'"
g year=`j'

rename UNIT unit
rename TKNNO tokenno
rename date day
g unit1=unit
tostring unit, replace

gen zeroC="0" if unit1<=9

egen unitstr=concat(zeroC unit) if  zeroC!=""
replace unitstr=unit if  unitstr==""

egen unittoken=concat(unitstr tokenno )
destring unit, replace
merge m:1 unit using "ledproject_unitnodup.dta"

keep if _merge==3
drop _merge zeroC unitstr
encode unittoken, gen(unittk)

save "eas_`x'`j'", replace
}
}


foreach x in jan mar may jul aug oct dec{

foreach j of numlist 2011 2012 {
import excel "eas_`x'`j'.xls", sheet("Sheet1") firstrow clear

rename K `x'1
rename L `x'2
rename M `x'3
rename N `x'4
rename O `x'5
rename P `x'6
rename Q `x'7
rename R `x'8
rename S `x'9
rename T `x'10
rename U `x'11
rename V `x'12
rename W `x'13
rename X `x'14
rename Y `x'15
rename Z `x'16
rename AA `x'17
rename AB `x'18
rename AC `x'19
rename AD `x'20
rename AE `x'21
rename AF `x'22
rename AG `x'23
rename AH `x'24
rename AI `x'25
rename AJ `x'26
rename AK `x'27
rename AL `x'28
 rename AM `x'29
 rename AN `x'30
 rename AO `x'31

tostring `x'29 `x'30 `x'31, replace

reshape long `x', i( UNIT TKNNO) j(date)
rename `x' attendance
g month="`x'"
g year=`j'

rename UNIT unit
rename TKNNO tokenno
rename date day
g unit1=unit
tostring unit, replace

gen zeroC="0" if unit1<=9

egen unitstr=concat(zeroC unit) if  zeroC!=""
replace unitstr=unit if  unitstr==""

egen unittoken=concat(unitstr tokenno )
destring unit, replace
merge m:1 unit using "ledproject_unitnodup.dta"

keep if _merge==3
drop _merge zeroC unitstr
encode unittoken, gen(unittk)
save "eas_`x'`j'", replace
}
}


foreach x in apr jun sep nov{

foreach j of numlist 2011 2012{
import excel "eas_`x'`j'.xls", sheet("Sheet1") firstrow clear

rename K `x'1
rename L `x'2
rename M `x'3
rename N `x'4
rename O `x'5
rename P `x'6
rename Q `x'7
rename R `x'8
rename S `x'9
rename T `x'10
rename U `x'11
rename V `x'12
rename W `x'13
rename X `x'14
rename Y `x'15
rename Z `x'16
rename AA `x'17
rename AB `x'18
rename AC `x'19
rename AD `x'20
rename AE `x'21
rename AF `x'22
rename AG `x'23
rename AH `x'24
rename AI `x'25
rename AJ `x'26
rename AK `x'27
rename AL `x'28
 rename AM `x'29
 rename AN `x'30

reshape long `x', i( UNIT TKNNO) j(date)
rename `x' attendance
g month="`x'"
g year=`j'

rename UNIT unit
rename TKNNO tokenno
rename date day
g unit1=unit
tostring unit, replace

gen zeroC="0" if unit1<=9

egen unitstr=concat(zeroC unit) if  zeroC!=""
replace unitstr=unit if  unitstr==""

egen unittoken=concat(unitstr tokenno )
destring unit, replace
merge m:1 unit using "ledproject_unitnodup.dta"

keep if _merge==3
drop _merge zeroC unitstr
encode unittoken, gen(unittk)
save "eas_`x'`j'", replace
}
}

foreach x in apr jun sep{

foreach j of numlist 2013{
import excel "eas_`x'`j'.xls", sheet("Sheet1") firstrow clear

rename K `x'1
rename L `x'2
rename M `x'3
rename N `x'4
rename O `x'5
rename P `x'6
rename Q `x'7
rename R `x'8
rename S `x'9
rename T `x'10
rename U `x'11
rename V `x'12
rename W `x'13
rename X `x'14
rename Y `x'15
rename Z `x'16
rename AA `x'17
rename AB `x'18
rename AC `x'19
rename AD `x'20
rename AE `x'21
rename AF `x'22
rename AG `x'23
rename AH `x'24
rename AI `x'25
rename AJ `x'26
rename AK `x'27
rename AL `x'28
 rename AM `x'29
 rename AN `x'30

reshape long `x', i( UNIT TKNNO) j(date)
rename `x' attendance
g month="`x'"
g year=`j'

rename UNIT unit
rename TKNNO tokenno
rename date day
g unit1=unit
tostring unit, replace

gen zeroC="0" if unit1<=9

egen unitstr=concat(zeroC unit) if  zeroC!=""
replace unitstr=unit if  unitstr==""

egen unittoken=concat(unitstr tokenno )
destring unit, replace
merge m:1 unit using "ledproject_unitnodup.dta"

keep if _merge==3
drop _merge zeroC unitstr
encode unittoken, gen(unittk)
save "eas_`x'`j'", replace
}
}


foreach x in feb{

foreach j of numlist 2011 2013 {
import excel "eas_`x'`j'.xls", sheet("Sheet1") firstrow clear

rename K `x'1
rename L `x'2
rename M `x'3
rename N `x'4
rename O `x'5
rename P `x'6
rename Q `x'7
rename R `x'8
rename S `x'9
rename T `x'10
rename U `x'11
rename V `x'12
rename W `x'13
rename X `x'14
rename Y `x'15
rename Z `x'16
rename AA `x'17
rename AB `x'18
rename AC `x'19
rename AD `x'20
rename AE `x'21
rename AF `x'22
rename AG `x'23
rename AH `x'24
rename AI `x'25
rename AJ `x'26
rename AK `x'27
rename AL `x'28

reshape long `x', i( UNIT TKNNO) j(date)
rename `x' attendance
g month="`x'"
g year=`j'

rename UNIT unit
rename TKNNO tokenno
rename date day
g unit1=unit
tostring unit, replace

gen zeroC="0" if unit1<=9

egen unitstr=concat(zeroC unit) if  zeroC!=""
replace unitstr=unit if  unitstr==""

egen unittoken=concat(unitstr tokenno )
destring unit, replace
merge m:1 unit using "ledproject_unitnodup.dta"

keep if _merge==3
drop _merge zeroC unitstr
encode unittoken, gen(unittk)
save "eas_`x'`j'", replace
}
}

foreach x in feb{

foreach j of numlist 2012 {
import excel "eas_`x'`j'.xls", sheet("Sheet1") firstrow clear

rename K `x'1
rename L `x'2
rename M `x'3
rename N `x'4
rename O `x'5
rename P `x'6
rename Q `x'7
rename R `x'8
rename S `x'9
rename T `x'10
rename U `x'11
rename V `x'12
rename W `x'13
rename X `x'14
rename Y `x'15
rename Z `x'16
rename AA `x'17
rename AB `x'18
rename AC `x'19
rename AD `x'20
rename AE `x'21
rename AF `x'22
rename AG `x'23
rename AH `x'24
rename AI `x'25
rename AJ `x'26
rename AK `x'27
rename AL `x'28
 rename AM `x'29
 rename AN `x'30
rename AO `x'31

tostring feb30 feb31, replace
reshape long `x', i( UNIT TKNNO) j(date)
rename `x' attendance
g month="`x'"
g year=`j'

rename UNIT unit
rename TKNNO tokenno
rename date day
g unit1=unit
tostring unit, replace

gen zeroC="0" if unit1<=9

egen unitstr=concat(zeroC unit) if  zeroC!=""
replace unitstr=unit if  unitstr==""

egen unittoken=concat(unitstr tokenno )
destring unit, replace
merge m:1 unit using "ledproject_unitnodup.dta"

keep if _merge==3
drop _merge zeroC unitstr
encode unittoken, gen(unittk)
save "eas_`x'`j'", replace
}
}


foreach x in jan mar may jul aug oct{

foreach j of numlist 2013{
import excel "eas_`x'`j'.xls", sheet("Sheet1") firstrow clear

rename K `x'1
rename L `x'2
rename M `x'3
rename N `x'4
rename O `x'5
rename P `x'6
rename Q `x'7
rename R `x'8
rename S `x'9
rename T `x'10
rename U `x'11
rename V `x'12
rename W `x'13
rename X `x'14
rename Y `x'15
rename Z `x'16
rename AA `x'17
rename AB `x'18
rename AC `x'19
rename AD `x'20
rename AE `x'21
rename AF `x'22
rename AG `x'23
rename AH `x'24
rename AI `x'25
rename AJ `x'26
rename AK `x'27
rename AL `x'28
 rename AM `x'29
 rename AN `x'30
 rename AO `x'31

tostring `x'29 `x'30 `x'31, replace
reshape long `x', i( UNIT TKNNO) j(date)
rename `x' attendance
g month="`x'"
g year=`j'

rename UNIT unit
rename TKNNO tokenno
rename date day
g unit1=unit
tostring unit, replace

gen zeroC="0" if unit1<=9

egen unitstr=concat(zeroC unit) if  zeroC!=""
replace unitstr=unit if  unitstr==""

egen unittoken=concat(unitstr tokenno )
destring unit, replace
merge m:1 unit using "ledproject_unitnodup.dta"

keep if _merge==3
drop _merge zeroC unitstr
encode unittoken, gen(unittk)
save "eas_`x'`j'", replace
}
}


foreach j of numlist 2010 2011 2012 {
use "eas_jan`j'", clear

foreach x in feb mar apr may jun jul aug sep oct nov dec{
append using "eas_`x'`j'"
}

drop if attendance==""
drop if attendance=="."

replace attendance="P" if attendance=="  P"
replace attendance="P" if attendance==" P"

replace attendance="A" if attendance=="  A"
replace attendance="A" if attendance==" A"

drop if attendance=="S" | attendance==" S"

drop if attendance=="H" | attendance==" H"

drop if attendance=="W/O" | attendance==" W/O"

drop if attendance=="M" | attendance==" M"
drop if attendance=="E" | attendance==" E"

replace attendance="L" if attendance==" L"

replace attendance="C" if attendance==" C"

drop if attendance=="SP" | attendance==" SP"
replace attendance="L" if attendance==" L"

g present=attendance=="P"

save "attendance`j'", replace

}


foreach j of numlist 2013{
use "eas_jan`j'", clear

foreach x in feb mar apr may jun jul aug sep oct{
append using "eas_`x'`j'"
}

drop if attendance==""
drop if attendance=="."

replace attendance="P" if attendance=="  P"
replace attendance="P" if attendance==" P"

replace attendance="A" if attendance=="  A"
replace attendance="A" if attendance==" A"

drop if attendance=="S" | attendance==" S"

drop if attendance=="H" | attendance==" H"

drop if attendance=="W/O" | attendance==" W/O"

drop if attendance=="M" | attendance==" M"
drop if attendance=="E" | attendance==" E"

replace attendance="L" if attendance==" L"

replace attendance="C" if attendance==" C"

drop if attendance=="SP" | attendance==" SP"
replace attendance="L" if attendance==" L"

g present=attendance=="P"

save "attendance`j'", replace

}
*keeping People in sewing only
use "attendance2010", clear

keep if SUBDEPT=="SEWING"
 drop NAME PAYDAYS LEAVE SUNDAYS HOLIDAYS ABS unit1 unittoken
save "attendance2010_sewing", replace

use "attendance2011", clear
rename SEUBDEPT SUBDEPT

keep if SUBDEPT=="SEWING"
 drop NAME PAYDAYS LEAVE SUNDAYS HOLIDAYS ABS unit1 unittoken
save "attendance2011_sewing", replace

use "attendance2012", clear
rename SEUBDEPT SUBDEPT
tab SUBDEPT, sort
keep if SUBDEPT=="SEWING"
 drop NAME PAYDAYS LEAVE SUNDAYS HOLIDAYS ABS unit1 unittoken
save "attendance2012_sewing", replace

use "attendance2013", clear
replace SUBDEPT= SEUBDEPT if SUBDEPT=="" & SEUBDEPT!=""
drop SEUBDEPT
tab SUBDEPT, sort
keep if SUBDEPT=="SEWING"
 drop NAME PAYDAYS LEAVE SUNDAYS HOLIDAYS ABS unit1 unittoken
save "attendance2013_sewing", replace

use "attendance2010_sewing",clear
foreach i of numlist 2011 2012 2013{
append using "attendance`i'_sewing"

}


replace month="1" if month=="jan"
replace month="2" if month=="feb"
replace month="3" if month=="mar"
replace month="4" if month=="apr"
replace month="5" if month=="may"
replace month="6" if month=="jun"
replace month="7" if month=="jul"
replace month="8" if month=="aug"
replace month="9" if month=="sep"
replace month="10" if month=="oct"
replace month="11" if month=="nov"
replace month="12" if month=="dec"
destring month, replace
tab month

g date=mdy(month, day, year)
drop if year==2010 & month<4
drop if year==2013 & month>6

merge m:1 unit using "leddetails.dta", gen(m1)
cap drop SUBDEPT AO E ledlightsquantityinnos installationdetails m1

g monthy=year+(month/12)
g monthyled=yearled+(monthled/12)

g led=monthy>monthyled
drop monthy monthyled

merge m:1 unit using "tamu_unitmerge", gen(mm)
drop mm
replace latitude=12.9575 if unit==22 & latitude==.
replace longitude=77.5 if unit==22 & longitude==.
tostring date, replace
drop monthled yearled orderled var7 neworder2 neworder neworderled newled elevation
merge m:1 longitude latitude year month day using "weather_2010to2013.dta ", gen(mmm) keepusing(meant himeant wbgtmeant relativehumidity precipitation)
keep if mmm==3

destring date, replace
g dow=dow(date)
tab dow
drop if dow==0
drop longitude latitude mmm
foreach var of varlist meant himeant wbgtmeant{
g `var'led=`var'*led
}

save "attendance_merged_tamu", replace